﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ASPNETDBModel;

/// <summary>
/// Summary description for DAL
/// </summary>
public class DAL
{
     ASPNETDBEntities _aspdbEntity = new ASPNETDBEntities();
	public DAL()
	{

	}

    /// <summary>
    /// Insert Test to DB
    /// </summary>
    /// <param name="testName">Name of test</param>
    /// <returns>true if adding was successfull</returns>
    public bool InsertTest(string testName,string CatId)
    {
        aspnet_Tests test = new aspnet_Tests();
        test.Name = testName;
        test.TestId = Guid.NewGuid();
        Guid guidCatId = new Guid(CatId);
        test.CategoryId=_aspdbEntity.aspnet_TestCategory.Where(c => c.CategoryId == guidCatId).Single().CategoryId;
        try
        {
            _aspdbEntity.AddToaspnet_Tests(test);
            _aspdbEntity.SaveChanges();
        }
        catch { return false;}

        return true;
    }
    /// <summary>
    /// Insert Category to DB
    /// </summary>
    /// <param name="catName">category name</param>
    /// <returns>true if adding was successfull</returns>
    public bool InsertTestCategory(string catName)
    {
        aspnet_TestCategory test = new aspnet_TestCategory();
        test.CategoryName = catName;
        test.CategoryId = Guid.NewGuid();

        try
        {
            _aspdbEntity.AddToaspnet_TestCategory(test);
            _aspdbEntity.SaveChanges();
        }
        catch { return false; }

        return true;
    }
    /// <summary>
    /// Insert questions to DB
    /// </summary>
    /// <param name="testName">Name of question</param>
    /// <returns>true if adding was successfull</returns>
    public bool InsertQuestion(string questName, string testId)
    {
        Guid guidTestId = new Guid(testId);

        aspnet_TestQuestions quest = new aspnet_TestQuestions();
        quest.Question = questName;
        quest.QuestionId = Guid.NewGuid();
        quest.TestId = _aspdbEntity.aspnet_Tests.Where(t => t.TestId == guidTestId).Single().TestId;
               
        try
        {
            _aspdbEntity.AddToaspnet_TestQuestions(quest);
            _aspdbEntity.SaveChanges();
        }
        catch { return false; }

        return true;
    }

    /// <summary>
    /// Insert questions to DB
    /// </summary>
    /// <param name="testName">Name of question</param>
    /// <returns>true if adding was successfull</returns>
    public bool InsertAnswer(string answerName, string testQuestionId, int point)
    {
        Guid guidQuestionId = new Guid(testQuestionId);

        aspnet_Answers answer = new aspnet_Answers();
        answer.Answer = answerName;
        answer.AnswerId = Guid.NewGuid();
        answer.QuestionId = _aspdbEntity.aspnet_TestQuestions.Where(q => q.QuestionId == guidQuestionId).Single().QuestionId;
        answer.Points = point;

        try
        {
            _aspdbEntity.AddToaspnet_Answers(answer);
            _aspdbEntity.SaveChanges();
        }
        catch { return false; }

        return true;
    }

    /// <summary>
    /// Insert result of test to DB
    /// </summary>
    /// <param name="testName">Name of result</param>
    /// <returns>true if adding was successfull</returns>
    public bool InsertResultTest(string resultName, string testId, int minPoint, int maxPoint)
    {
        Guid guidTestId = new Guid(testId);

        aspnet_TestResults result = new aspnet_TestResults();
        result.Result = resultName;
        result.ResultId = Guid.NewGuid();
        result.TestId = _aspdbEntity.aspnet_Tests.Where(t => t.TestId == guidTestId).Single().TestId;
        result.MinPoint = minPoint;
        result.MaxPoint = maxPoint;

        try
        {
            _aspdbEntity.AddToaspnet_TestResults(result);
            _aspdbEntity.SaveChanges();
        }
        catch { return false; }

        return true;
    }


    /// <summary>
    /// Insert  User results of Test 
    /// </summary>
    /// <param name="testId">Test id</param>
    /// <param name="userId">User Id</param>
    /// <param name="points">Total points</param>
    /// <returns>true if adding was successfull</returns>
    public bool InsertUserTestResult(string testId, string userId, int points)
    {
        Guid guidTestId = new Guid(testId);
        Guid guidUserId = new Guid(userId);
        aspnet_UserTestResults result = new aspnet_UserTestResults();

        result.TestId = guidTestId;
        result.ResultId = Guid.NewGuid();
        result.UserId = guidUserId;
        result.TotalPoints = points;
        result.Date = DateTime.Now;

        try
        {
            _aspdbEntity.AddToaspnet_UserTestResults(result);
            _aspdbEntity.SaveChanges();
        }
        catch { return false; }

        return true;
    }

    public bool InsertVoteQuestion(Guid testId, Guid questId, int vote)
    {
        aspnet_VoteQuestion voteQ = new aspnet_VoteQuestion();


        aspnet_VoteQuestion PrevVotes = _aspdbEntity.aspnet_VoteQuestion.Where(vq => vq.QuestionId==questId).FirstOrDefault();
        voteQ.VoteId = Guid.NewGuid();
        voteQ.TestId = testId;
        voteQ.QuestionId = questId;

        try
        {
            if (PrevVotes == null)
            {
                voteQ.ValueVotes = vote;
                _aspdbEntity.AddToaspnet_VoteQuestion(voteQ);
                _aspdbEntity.SaveChanges();
            }
            else
            {
                PrevVotes.ValueVotes = (PrevVotes.ValueVotes + vote) / 2;
                _aspdbEntity.SaveChanges();
            }
        }
        catch { return false;}

        return true;

    }

    public bool SetTestVote(string testId, int vote)
    {
 
        aspnet_Tests PrevVotes = _aspdbEntity.aspnet_Tests.Where(t => t.TestId == (new Guid(testId))).Single();


        try
        {
            if (string.IsNullOrEmpty(PrevVotes.ValueVote.ToString()))
            {
                PrevVotes.ValueVote = vote;
                _aspdbEntity.SaveChanges();
            }
            else
            {
                PrevVotes.ValueVote = (PrevVotes.ValueVote + vote) / 2;
                _aspdbEntity.SaveChanges();
            }
        }
        catch { return false; }

        return true;

    }

    /////////////////////////////////////////////////SELECT////////////////////////////////////////////////////////////////////

    /// <summary>
    /// Select all answers by question
    /// </summary>
    /// <param name="questionID">ID question</param>
    /// <returns>List of anwer</returns>
    public List<aspnet_Answers> SelectAnswersByQuestion(string questionID)
    {
        Guid guidQuestId = new Guid(questionID);
        return _aspdbEntity.aspnet_Answers.Where(a => a.QuestionId == guidQuestId).ToList();
    }

    /// <summary>
    /// Select all question by test
    /// </summary>
    /// <param name="questionID">ID question</param>
    /// <returns>List of anwer</returns>
    public List<aspnet_TestQuestions> SelectQuestionsByTest(string testID)
    {
        Guid guidTestId = new Guid(testID);
        return _aspdbEntity.aspnet_TestQuestions.Where(q => q.TestId == guidTestId).ToList();
    }

    /// <summary>
    /// Get result of test
    /// </summary>
    /// <param name="testID">ID test</param>
    /// <returns></returns>
    /// <param name="point">Point of your test</param>
    /// <returns>result</returns>
    public string SelectResultByTest(string testID, int point)
    {
        Guid guidTestId = new Guid(testID);
        Dictionary<int, int> minMaxPoints = new Dictionary<int, int>();
        return _aspdbEntity.aspnet_TestResults.Where(res => res.TestId == guidTestId && (point >= res.MinPoint && point <= res.MaxPoint)).Single().Result;
    }

   

    /// <summary>
    /// Select User Id By Name
    /// </summary>
    /// <param name="name">test name</param>
    /// <returns>result</returns>
    public string SelectUserIdByName(string name)
    {
        return _aspdbEntity.aspnet_Users.Where(u => u.UserName == name).Single().UserId.ToString();
    }

    /// <summary>
    /// Select Test Name By ID
    /// </summary>
    /// <param name="testId">ID test</param>
    /// <returns>result</returns>
    public string SelectTestNameByID(string testId)
    {
        return _aspdbEntity.aspnet_Tests.Where(t => t.TestId == (new Guid(testId))).Single().Name;
    }

    /// <summary>
    /// Select Test Name By ID
    /// </summary>
    /// <param name="testId">ID test</param>
    /// <returns>result</returns>
    public string SelectValueVotesByQuestID(string questId)
    {
        var votes= _aspdbEntity.aspnet_VoteQuestion.Where(v => v.QuestionId == (new Guid(questId))).FirstOrDefault();
        if (votes != null)
        {
            return votes.ValueVotes.ToString();
        }
        else
        {
            return null;
        }
    }
    /// <summary>
    /// Select Point of answer by id
    /// </summary>
    /// <param name="answID">answer id</param>
    /// <returns>points</returns>
    public int SelectPointsByAnswerID(string answID)
    {
        Guid guidAnswId = new Guid(answID);
        return _aspdbEntity.aspnet_Answers.Where(ans => ans.AnswerId == guidAnswId).Single().Points;
    }
    /// <summary>
    /// Select all Categories
    /// </summary>
    /// <returns>Categories</returns>
    public List<aspnet_TestCategory> SelectAllCategories()
    {
        return _aspdbEntity.aspnet_TestCategory.ToList();
    }

    /// <summary>
    /// Select Tests By Category id
    /// </summary>
    /// <param name="catId">category ID</param>
    /// <returns>Tests</returns>
    public List<aspnet_Tests> SelectTestsByCategoryId(string catId)
    {
        return _aspdbEntity.aspnet_Tests.Where(t => t.CategoryId == (new Guid(catId))).ToList();
    }


    /// <summary>
    /// Select all test ID
    /// </summary>
    /// <returns></returns>
    public List<string> SelectAllTestId()
    {
        List<Guid> tempGuidsId = _aspdbEntity.aspnet_Tests.Select(t2 => t2.TestId).ToList();
        List<string> listId = new List<string>();
        foreach (Guid g in tempGuidsId)
        {
            listId.Add(g.ToString());
        }
        return listId;
    }

    /// <summary>
    /// Select VotesVale of test
    /// </summary>
    /// <param name="testId"></param>
    /// <returns></returns>
    public string SelectTestVote(string testId)
    {
        return _aspdbEntity.aspnet_Tests.Where(t => t.TestId == (new Guid(testId))).Single().ValueVote.ToString();
    }

    public List<DateResultTest> SelectDataToTable(string username)
    {
        Guid userId=_aspdbEntity.aspnet_Users.Where(u => u.UserName == username).Single().UserId;
        var Listutr = from us in _aspdbEntity.aspnet_UserTestResults
                      where us.UserId == userId
                      group us by  us.TestId into g
                      select new
                      {
                          TestId = g.Key,
                          Points=g.Where(p=>p.Date==(g.Max(d => d.Date))).FirstOrDefault().TotalPoints,
                          date = g.Max(d => d.Date)
                      };
        List<DateResultTest> listDateResult = new List<DateResultTest>();
        foreach (var t in Listutr)
        {
           string testName = SelectTestNameByID(t.TestId.ToString());
           string testResult = SelectResultByTest(t.TestId.ToString(), t.Points);
           listDateResult.Add(new DateResultTest(testName, testResult, t.date));
        }
            
        return listDateResult;
    }
    public void InsertProblimVictory(string name, string problem, string victory)
    {
        Guid guid = _aspdbEntity.aspnet_Users.Where(u => u.UserName == name).Single().UserId;
        _aspdbEntity.AddToProblemVictory(new ProblemVictory() { ProblemID = Guid.NewGuid(), UserID = guid, YouProblem = problem, YouVictory = victory });
        _aspdbEntity.SaveChanges();
    }
    public List<Question> getQuestion() {
        return _aspdbEntity.Question.ToList();
        
    }
    public List<Answer> getAnswer(int QuestionID)
    {
       //_aspdbEntity.Answer.GetType()
        return _aspdbEntity.Answer.Where(a => a.QuestionID == QuestionID).ToList();
        
    }

}


public class DateResultTest
{
    string testName;
    DateTime date;
    string testResult;

    public DateResultTest()
    {

    }

    public DateResultTest(string tName, string tResult, DateTime dtime)
    {
        TestName = tName;
        TestResult = tResult;
        Date = dtime;
    }
    public string TestName
    {
        get { return testName; }
        set { testName = value; }
    }
    public string TestResult
    {
        get { return testResult; }
        set { testResult = value; }
    }
    public DateTime Date
    {
        get { return date; }
        set { date = value; }
    }
}